﻿using Akso.DataImporting.Excel.EpPlus;
using Akso.Dto;
using Akso.Entities;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Akso.Inbounds.Importing
{
    public class InboundExcelImport : EpPlusExcelImporterBase, IInboundExcelImport
    {
        //Import Excel to Database
        public string ImportToDB(string filePath)
        {
            ICollection<Inbound> inboundList = new List<Inbound>();
            int titleRow = 3; //表头开始行
            string tableName = "Inbounds";//表名
            ExcelPackage package = null;
            ExcelWorksheet sheet = null;
            try
            {
                //读取excel
                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    package = new ExcelPackage(fs);
                    //ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Inbound");
                    sheet = package.Workbook.Worksheets[1];
                }

                #region check excel format
                string errorMsg = string.Empty;
               
                //验证表头
                if (sheet.Cells[titleRow, 1] == null || !sheet.Cells[titleRow, 1].Value.Equals("Vendor"))
                {
                    //errorMsg += "文件不符合规范！第1列不是Vendor列！";
                    errorMsg +=L("ExcelCellErrorMsg", 1,"Vendor");
                }
                if (sheet.Cells[titleRow, 2] == null || !sheet.Cells[titleRow, 2].Value.Equals("Inbound Delivery"))
                {
                    //errorMsg += "文件不符合规范！第2列不是Inbound Delivery列！";
                    errorMsg += L("ExcelCellErrorMsg", 2, "Inbound Delivery");
                }
                if (sheet.Cells[titleRow, 3] == null || !sheet.Cells[titleRow, 3].Value.Equals("Invoice number"))
                {
                    //errorMsg += "文件不符合规范！第3列不是Invoice number列！";
                    errorMsg += L("ExcelCellErrorMsg", 3, "Invoice number");
                }
                if (sheet.Cells[titleRow, 4] == null || !sheet.Cells[titleRow, 4].Value.Equals("Material No"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 4, "Material No");
                }
                if (sheet.Cells[titleRow, 5] == null || !sheet.Cells[titleRow, 5].Value.Equals("Material's English name"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 5, "Material's English name");
                }
                if (sheet.Cells[titleRow, 6] == null || !sheet.Cells[titleRow, 6].Value.Equals("Material's Chinese name"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 6, "Material's Chinese name");
                }
                if (sheet.Cells[titleRow, 7] == null || !sheet.Cells[titleRow, 7].Value.Equals("Qty"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 7, "Qty");
                }
                if (sheet.Cells[titleRow, 8] == null || !sheet.Cells[titleRow, 8].Value.Equals("Net Weight"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 8, "Net Weight");
                }
                if (sheet.Cells[titleRow, 9] == null || !sheet.Cells[titleRow, 9].Value.Equals("Gross Weight"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 9, "Gross Weight");
                }
                if (sheet.Cells[titleRow, 10] == null || !sheet.Cells[titleRow, 10].Value.Equals("Net Price"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 10, "Net Price");
                }
                if (sheet.Cells[titleRow, 11] == null || !sheet.Cells[titleRow, 11].Value.Equals("Amount"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 11, "Amount");
                }
                if (sheet.Cells[titleRow, 12] == null || !sheet.Cells[titleRow, 12].Value.Equals("Currency"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 12, "Currency");
                }
                if (sheet.Cells[titleRow, 13] == null || !sheet.Cells[titleRow, 13].Value.Equals("Specification"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 13, "Specification");
                }
                if (sheet.Cells[titleRow, 14] == null || !sheet.Cells[titleRow, 14].Value.Equals("Inbound Create Date"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 14, "Inbound Create Date");
                }
                if (sheet.Cells[titleRow, 15] == null || !sheet.Cells[titleRow, 15].Value.Equals("PO Number"))
                {
                    errorMsg += L("ExcelCellErrorMsg", 15, "PO Number");
                }

                if (!string.IsNullOrEmpty(errorMsg))
                {
                    return errorMsg;
                }
                #endregion
                int lastRow = sheet.Dimension.End.Row;
                #region 读取excel数据
                for (int i = titleRow + 1; i <= lastRow; i++)
                {
                    try
                    {
                        //这个写法要修改，否则无法对具体单元格异常进行捕捉
                        inboundList.Add(new Inbound
                        {
                            Vendor = sheet.Cells[i, 1].Value.ToString(),
                            Delivery = sheet.Cells[i, 2].Value.ToString(),
                            InvoiceNumber = sheet.Cells[i, 3].Value.ToString(),
                            MaterialNo = sheet.Cells[i, 4].Value.ToString(),
                            MaterialNameEn = sheet.Cells[i, 5].Value.ToString(),
                            MaterialNameCn = sheet.Cells[i, 6].Value.ToString(),
                            Qty = (sheet.Cells[i, 7].Value == null ? 0 : int.Parse(sheet.Cells[i, 7].Value.ToString())),
                            NetWeight = (sheet.Cells[i, 8].Value == null ? 0 : decimal.Parse(sheet.Cells[i, 8].Value.ToString())),
                            GrossWeight = (sheet.Cells[i, 9].Value == null ? 0 : decimal.Parse(sheet.Cells[i, 9].Value.ToString())),
                            NetPrice = (sheet.Cells[i, 10].Value == null ? 0 : decimal.Parse(sheet.Cells[i, 10].Value.ToString())),
                            Amount = (sheet.Cells[i, 11].Value == null ? 0 : decimal.Parse(sheet.Cells[i, 11].Value.ToString())),
                            Currency = sheet.Cells[i, 12].Value.ToString(),
                            Specification = sheet.Cells[i, 13].Value.ToString(),
                            InboundCreateDate = sheet.Cells[i, 14].Value.ToString(),
                            PONumber = sheet.Cells[i, 15].Value.ToString()
                        });
                    }
                    catch (Exception ex)
                    {
                        return "第"+i+"行数据有问题"+ ex.Message;
                    }
                }
                #endregion
                if (inboundList.Count == 0)
                {
                    return "没有需要导入的数据";
                }
                #region 数据导入
                string connStr = "Server =meme-pc; User ID=sa; Password=sql2012; Database=LOreal";
                //SqlConnection connectionSource = new SqlConnection(connStr);
               
                //导入到临时的表
                using (SqlBulkCopy obc = new SqlBulkCopy(connStr, SqlBulkCopyOptions.Default))
                {
                    DataTable dt = new DataTable();
                    DataRow dra;
                    dt.Columns.Add("Id", typeof(Guid));
                    dt.Columns.Add("Vendor", typeof(string));
                    dt.Columns.Add("Delivery", typeof(string));
                    dt.Columns.Add("InvoiceNumber", typeof(string));
                    dt.Columns.Add("MaterialNo", typeof(string));
                    dt.Columns.Add("MaterialNameEn", typeof(string));
                    dt.Columns.Add("MaterialNameCn", typeof(string));

                    dt.Columns.Add("Qty", typeof(int));
                    dt.Columns.Add("NetWeight", typeof(decimal));
                    dt.Columns.Add("GrossWeight", typeof(decimal));
                    dt.Columns.Add("NetPrice", typeof(decimal));
                    dt.Columns.Add("Amount", typeof(decimal));
                    dt.Columns.Add("Currency", typeof(string));
                    dt.Columns.Add("Specification", typeof(string));
                    dt.Columns.Add("InboundCreateDate", typeof(string));
                    dt.Columns.Add("PONumber", typeof(string));

                    dt.Columns.Add("IsDeleted", typeof(Boolean));
                    dt.Columns.Add("CreationTime", typeof(DateTime));
                    //dt.Columns.Add("CreatorUserId", typeof(long));
                    foreach (var item in inboundList)
                    {
                        dra = dt.NewRow();
                        dra["Id"] = Guid.NewGuid();
                        dra["Vendor"] = item.Vendor;
                        dra["Delivery"] = item.Delivery;
                        dra["InvoiceNumber"] = item.InvoiceNumber;
                        dra["MaterialNo"] = item.MaterialNo;
                        dra["MaterialNameEn"] = item.MaterialNameEn;
                        dra["MaterialNameCn"] = item.MaterialNameCn;

                        dra["Qty"] = item.Qty;
                        dra["NetWeight"] = item.NetWeight;
                        dra["GrossWeight"] = item.GrossWeight;
                        dra["NetPrice"] = item.NetPrice;
                        dra["Amount"] = item.Amount;
                        dra["Currency"] = item.Currency;
                        dra["Specification"] = item.Specification;
                        dra["InboundCreateDate"] = item.InboundCreateDate;
                        dra["PONumber"] = item.PONumber;
                        
                        dra["IsDeleted"] = false; 
                        dra["CreationTime"] = DateTime.Now;
                        //dra["CreatorUserId"] = "2";
                        dt.Rows.Add(dra);
                    }
                    obc.BatchSize = 5000;//每次传输的行数
                                         //obc.BulkCopyTimeout = 1800;
                    foreach (DataColumn colum in dt.Columns)
                    {
                        obc.ColumnMappings.Add(colum.ColumnName, colum.ColumnName);
                    }
                    obc.DestinationTableName = tableName;//表名
                    obc.WriteToServer(dt);
                }
                #endregion
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return string.Empty;
        }
    }
}
